from code.data.File import File
from code.dao.user_management import __check_user_exist
from code.dao.config import mysql_conn, cursor

import logging
import pymysql
import traceback
import datetime


def add_file(file: File):
    if file.userID is None:
        logging.error("UserID is required.")
        return False
    if not __check_user_exist(file.userID):
        return False
    if file.filename is None:
        logging.error("Filename is required.")
        return False
    if file.path is None:
        logging.error("Path is required.")
        return False
    if file.size is None:
        logging.error("Size is required.")
        return False
    path = file.path.replace("\\","\\\\")
    sql = "INSERT INTO file(filename, path, size, userID) VALUES ('{}', '{}', '{}', '{}')".format(
        file.filename, path, file.size, file.userID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Inserting file into database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("File is added with SQL: " + sql)
    return True


def search_file(file: File, search_by: str = "all", fuzzy_search: bool = False):
    files = []
    if search_by == "all":
        sql = "SELECT * FROM file"
    elif search_by == "fileID":
        if file.fileID is None:
            logging.error("FileID is required.")
            return None
        sql = "SELECT * FROM file WHERE fileID = {}".format(file.fileID)
    elif search_by == "filename":
        if file.filename is None:
            logging.error("Filename is required.")
            return None
        sql = "SELECT * FROM file WHERE filename = '{}'".format(file.filename)
        if fuzzy_search:
            sql = "SELECT * FROM file WHERE filename LIKE '%{}%'".format(file.filename)
    elif search_by == "time":
        if file.time is None:
            logging.error("Time is required.")
            return None
        sql = "SELECT * FROM file WHERE time = '{}'".format(file.time)
        if fuzzy_search:
            sql = "SELECT * FROM file WHERE time LIKE '%{}%'".format(file.time)
    elif search_by == "path":
        if file.path is None:
            logging.error("Path is required.")
            return None
        path = file.path.replace("\\", "\\\\")
        sql = "SELECT * FROM file WHERE path = '{}'".format(path)
        if fuzzy_search:
            sql = "SELECT * FROM file WHERE path LIKE '%{}%'".format(path)
    elif search_by == "size":
        if file.size is None:
            logging.error("Size is required.")
            return None
        sql = "SELECT * FROM file WHERE size = '{}'".format(file.size)
        if fuzzy_search:
            sql = "SELECT * FROM file WHERE size LIKE '%{}%'".format(file.size)
    elif search_by == "userID":
        if file.userID is None:
            logging.error("UserID is required.")
            return None
        sql = "SELECT * FROM file WHERE userID = '{}'".format(file.userID)
    else:
        logging.error("Attribute for search_by is not exist, use one in "
                      '["all", "fileID", "filename", "time", "path", "size", "userID"]')
        return None
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.warning("File is not exist.")
            return None
    except pymysql.Error as e:
        logging.error("Searching for file failed: " + str(e.args[1]))
        traceback.print_exc()
        return None
    for result in results:
        files.append(File(result[0], result[1], result[2], result[3], result[4], result[5]))
    logging.info("Search file(s) with SQL: {}".format(sql))
    return files


def delete_file(file: File):
    if file.fileID is None:
        logging.error("FileID is required.")
        return False
    if not __check_file_exist(file.fileID):
        return False
    sql = "DELETE FROM file WHERE fileID = {}".format(file.fileID)
    try:
        cursor.execute(sql)
        mysql_conn.commit()
    except pymysql.Error as e:
        logging.error("Delete file from database failed: " + str(e.args[1]))
        mysql_conn.rollback()
        return False
    logging.info("File is deleted with SQL: {}".format(sql))
    return True


def __check_file_exist(fileID: int):
    sql = "SELECT * FROM file WHERE fileID = {}".format(fileID)
    try:
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            logging.error("File {} is not exist".format(fileID))
            return False
    except pymysql.Error as e:
        logging.error("Searching for file failed: " + str(e.args[1]))
        traceback.print_exc()
        return False
    return True


if __name__ == "__main__":
    file1 = File(filename="test.file", path="D:\\test.file", size="145", userID="admin")
    file2 = File(filename="test.file(2)", path="D:\\test.file(2)", size="145", userID="admin")
    print(add_file(file1))
    print(add_file(file2))
    results1 = search_file(File(), "all")
    results2 = search_file(File(filename="test"), "fake")
    results3 = search_file(File(filename="test.file"), "filename")
    results4 = search_file(File(filename="test.file"), "filename", fuzzy_search=True)
    print(results1)
    print(results2)
    print(results3)
    print(results4)
    for result in results1:
        print(result.to_string())
    for result in results3:
        print(result.to_string())
    for result in results4:
        print(result.to_string())
    print(delete_file(File(fileID=1)))
